package kr.or.kead.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import kr.or.kead.domain.Professor;
import kr.or.kead.utils.MySqlCon;

public class DaoProfessor implements DaoTable {

	@Override
	public int insertDao(Object obj) {
		Connection con = MySqlCon.getConn();
		Professor professor = (Professor) obj;
		String sql = "insert into professor(code, name, depart, course, email) values(?,?,?,?,?)";
		PreparedStatement pstmt=null;
		try {
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1, professor.getCode());
			pstmt.setString(2, professor.getName());
			pstmt.setInt(3, professor.getDepartCode());
			pstmt.setString(4, professor.getCourse());
			pstmt.setString(5, professor.getEmail());
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public int updateDao(Object obj) {
		Connection con = MySqlCon.getConn();
		Professor professor = (Professor) obj;
		String sql = "update professor set name=?, depart=?, course=?, email=? where code=?";
		PreparedStatement pstmt = null;
		try {
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1, professor.getName());
			pstmt.setInt(2, professor.getDepartCode());
			pstmt.setString(3, professor.getCourse());
			pstmt.setString(4, professor.getEmail());
			pstmt.setInt(5, professor.getCode());
			pstmt.execute();
		} catch (SQLException e) {
			return -1;
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public int deleteDao(int regNo) {
		Connection con = MySqlCon.getConn();
		
		String sql = "delete from professor where code=?";
		PreparedStatement pstmt=null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, regNo);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public ArrayList<Object> selectDao() {
		Connection con = MySqlCon.getConn();
		String sql = "select code,name,depart,course,email from professor";
		ArrayList<Object> professors = new ArrayList<>();
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()){
				professors.add(new Professor(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4),rs.getString(5)));
			}
			return professors;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	@Override
	public Object selectTableById(int stdId) {
		Connection con = MySqlCon.getConn();
		String sql = "select code,name,depart,course,email,passwd from professor where code=?";
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, stdId);
			System.out.println(pstmt);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()){
				return new Professor(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4),rs.getString(5),rs.getString(6));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	
	@Override
	public ArrayList<String> selectTableAllList() {
		Connection con = MySqlCon.getConn();
		
		String sql = "select code,name from professor";
		ArrayList<String> professors = new ArrayList<>();
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()){
				professors.add(rs.getInt(1)+":"+rs.getString(2));
			}
			return professors;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public Object selectCodeNameByid(int code) {
		Connection con = MySqlCon.getConn();
		
		String sql = "select code,name from professor where code=?";
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, code);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()){
				return (rs.getInt(1)+":"+rs.getString(2));
			}

		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	
	public int selectMaxIdx(){
		Connection con = MySqlCon.getConn();
		String sql = "select max(code) from professor";
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()){
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return -1;
	}
	
	@Override
	public Object selectTableByEmail(String email) {
		Connection con = MySqlCon.getConn();
		String sql = "select code,name,depart,course,email from professor where email=?";
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, email);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()){
				return new Professor(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4), rs.getString(5));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	
	@Override
	public int updataTableByEmail(String pass, String email) {
		Connection con = MySqlCon.getConn();	
		String sql = "update professor set passwd=? where email=?";
		PreparedStatement pstmt=null;
		try {
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1, pass);
			pstmt.setString(2, email);
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public ArrayList<String> selectTableAllNames() {
		// TODO Auto-generated method stub
		return null;
	}

	public Object selectTableIdxNameByCode(int prof_code) {
		Connection con = MySqlCon.getConn();
		String sql = "select code, name from professor where code=?";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, prof_code);
			rs = pstmt.executeQuery();
			if (rs.next()) return rs.getInt(1)+" : "+rs.getString(2);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				rs.close();
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	public ArrayList<String> selectTableAllIdx() {
		Connection con = MySqlCon.getConn();

		String sql = "select code, name from professor";
		ArrayList<String> professors = new ArrayList<>();
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) professors.add(rs.getInt(1)+" : "+rs.getString(2));
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return professors;
	}
}
